package db.jdbc;

import db.ClubRepository;
import domain.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;


/**
 * 社团资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcClubRepository implements ClubRepository {
    private static final String INSERT_Club = "insert into club(clubName,college,description,createTime) values(?,?,?,?)";
    private static final String INSERT_Club_ClubAdministrators = "insert into club_clubAdministrators(clubID,clubAdminID) values(?,?)";
    private static final String DELETE_Club = "delete from club where clubID = ?";
    private static final String DELETE_Club_ClubAdministrators = "delete from club_clubAdministrators where clubID = ?";
    private static final String DELETE_Club_Users = "delete from club_users where clubID = ?";
    private static final String DELETE_club_activities = "delete from club_activities where clubID = ?";
    private static final String DELETE_club_clubApplication = "delete from club_clubApplication where clubID = ?";
    private static final String UPDATE_Club = "update club set clubName= ?, college= ?, description= ? where clubID = ?";
    private static final String SELECT_Club = "select * from club";
    private static final String SELECT_Club_BY_PAGE = SELECT_Club + " order by clubID  limit ? offset  ?";
    private JdbcTemplate jdbc;

    @Autowired
    public JdbcClubRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取社团的总数
     *
     * @return 社团的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(clubID) from club");
    }

    /**
     * 创建一个新的社团
     *
     * @param club 新社团的信息
     * @return 新创建的社团
     */
    @Override
    public Club save(Club club) {
        Vector<ClubAdministrator> adminList = club.getAdminList();
        for (ClubAdministrator admin : adminList) {
            jdbc.update(INSERT_Club_ClubAdministrators, club.getClubID(), admin.getClubAdminID());
        }
        jdbc.update(INSERT_Club, club.getClubName(),
                club.getCollege(), club.getDescription(),
                club.getCreateTime());
        return club;
    }

    /**
     * 根据ID查找社团
     *
     * @param clubID 社团的ID
     * @return 对应ID的社团
     */
    @Override
    public Club findOne(int clubID) {
        Club club = null;
        List<ClubAdministrator> admins =null;
        List<User> members=null;
        List<Activity> activities=null;
        try {
            club = jdbc.queryForObject(SELECT_Club + " where clubID = ?", new ClubRowMapper(), clubID);
            admins=jdbc.query("select ca.* from clubAdministrator as ca, club_clubAdministrators as cc where cc.clubID=? and cc.clubAdminID=ca.clubAdminID",new JdbcClubAdministratorRepository.ClubAdministratorRowMapper(),clubID);
            members=jdbc.query("select u.* from _user as u,club_users as cu where cu.clubID=? and cu.userID=u.userID",new JdbcUserRepository.UserRowMapper(),clubID);
            activities=jdbc.query("select a.* from activity as a,club_activities as ca where ca.clubID=? and ca.activityID=a.activityID",new JdbcActivityRepository.ActivityRowMapper(),clubID);
            Vector<ClubAdministrator> adminList=new Vector<>(admins);
            Vector<User> memberList=new Vector<>(members);
            Vector<Activity> activityList=new Vector<>(activities);
            club.setAdminList(adminList);
            club.setMemberList(memberList);
            club.setActivityList(activityList);
        } catch (DataAccessException e) {
        }
        return club;
    }

    /**
     * 根据社团管理员查找社团
     *
     * @param clubAdministrator 社团管理员
     * @return 对应社团管理员的社团
     */
    @Override
    public Club findOne(ClubAdministrator clubAdministrator) {
        int clubID=jdbc.queryForInt("select clubID from club_clubAdministrators where clubAdminID=?",clubAdministrator.getClubAdminID());
        return jdbc.queryForObject("select* from club where clubID=?", new ClubRowMapper(), clubID);
    }

    /**
     * 根据社团名查找社团
     *
     * @param clubName 社团名
     * @return 对应社团名的社团
     */
    @Override
    public Club findByClubName(String clubName) {
        Club club = null;
        try {
            club = jdbc.queryForObject(SELECT_Club + " where clubName = ?", new ClubRowMapper(), clubName);
        } catch (DataAccessException e) {
        }
        return club;
    }

    /**
     * 分页查找社团
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的社团列表
     */
    @Override
    public PaginationSupport<Club> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<Club>(new ArrayList<Club>(0), 0);
        }

        List<Club> items = jdbc.query(SELECT_Club_BY_PAGE, new ClubRowMapper(), pageSize, startIndex);
        return new PaginationSupport<Club>(items, totalCount, pageSize, startIndex);
    }

    /**
     * 审批社团
     *
     * @param clubID         社团ID
     * @param approvalStatus 审批状态
     * @return 是否审批成功
     */
    @Override
    public boolean approveClub(int clubID, int approvalStatus) {
        int clubApplicationID = jdbc.queryForInt("select clubApplicationID from club_clubApplication where clubID=?", clubID);
        return jdbc.update("update clubApplication set status=? where clubApplicationID=?", approvalStatus, clubApplicationID) != 0;
    }

    /**
     * 查看社团活动
     *
     * @param clubID 社团ID
     * @return 社团的活动列表
     */
    @Override
    public List<Activity> viewClubActivities(int clubID) {
        Club club = null;
        List<Activity> activities = new ArrayList<Activity>();
        try {
            club = jdbc.queryForObject(SELECT_Club + " where clubID=?", new ClubRowMapper(), clubID);
            activities = club.getActivityList();
        } catch (DataAccessException e) {
        }
        return activities;
    }

    /**
     * 审批社团活动
     *
     * @param activityId     活动ID
     * @param approvalStatus 审批状态
     * @return 是否审批成功
     */
    @Override
    public boolean approveClubActivity(int activityId, int approvalStatus) {
        int activityApplicationID = jdbc.queryForInt("select activityApplicationID from activity_activityApplication where activityID=?", activityId);
        return jdbc.update("update activityApplication set status=? where activityApplicationID=?", approvalStatus, activityApplicationID) != 0;
    }

    /**
     * 删除社团
     *
     * @param clubID 社团ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteClub(int clubID) {
        jdbc.update(DELETE_Club_Users, clubID);
        jdbc.update(DELETE_club_activities, clubID);
        jdbc.update(DELETE_club_clubApplication, clubID);
        return jdbc.update(DELETE_Club_ClubAdministrators, clubID) != 0 &&
                jdbc.update(DELETE_Club, clubID) != 0;
    }

    /**
     * 获取所有社团
     *
     * @return 所有社团的列表
     */
    @Override
    public List<Club> findAll() {
        return jdbc.query(SELECT_Club + " order by clubID", new ClubRowMapper());
    }

    /**
     * 列出成员列表
     *
     * @param clubID 社团ID
     * @return 社团的成员列表
     */
    @Override
    public List<User> listMembers(int clubID) {

        return jdbc.query("select u.* from _user as u,club_users as cu where cu.clubID=? and cu.userID=u.userID", new JdbcUserRepository.UserRowMapper(), clubID);
    }

    /**
     * 入团审批
     *
     * @param userId         用户ID
     * @param clubID         社团ID
     * @param approvalStatus 审批状态
     * @return 是否审批成功
     */
    @Override
    public boolean approveMembership(int userId, int clubID, int approvalStatus) {
        int membershipApplicationID = jdbc.queryForInt("select membershipApplicationID from user_membershipApplication where userID=?", userId);
        return jdbc.update("update membershipApplication set requestedClubID=?,status=? where membershipApplicationID=?", clubID, approvalStatus, membershipApplicationID) != 0;
    }

    /**
     * 根据用户查找社团
     *
     * @param userID 用户ID
     * @return 用户所在的社团列表
     */
    @Override
    public List<Club> findByUserId(int userID) {
        return jdbc.query("select c.* from club as c,club_users as cu where cu.userID=? and cu.clubID=c.clubID", new ClubRowMapper(), userID);
    }

    /**
     * 根据用户查找用户所有申请记录
     *
     * @param userID 用户ID
     * @return 用户的申请记录列表
     */
    @Override
    public List<MembershipApplication> findApplicationsByUserId(int userID) {
        return jdbc.query("select m.* from membershipApplication as m,applicant_membershipApplication as am where am.userID=? and am.membershipApplicationID=m.membershipApplicationID", new JdbcMembershipApplicationRepository.MembershipApplicationRowMapper(), userID);
    }

    /**
     * 根据用户退出社团
     *
     * @param userId 用户ID
     * @param clubId 社团ID
     * @return 是否退出成功
     */
    @Override
    public boolean quitClub(int userId, int clubId) {
        return jdbc.update("delete from club_users where userID=? and clubID=?", userId, clubId) != 0;
    }

    /**
     * 更新社团信息
     *
     * @param clubID      社团ID
     * @param clubName    社团名
     * @param college     学院
     * @param description 社团描述
     * @param createTime  创建时间
     * @return 是否更新成功
     */
    @Override
    public boolean updateClub(int clubID, String clubName, String college, String description, Date createTime) {
        return jdbc.update(UPDATE_Club, clubName, college, description, clubID) != 0;
    }

    /**
     * 查看社团管理员的社团信息
     *
     * @param clubAdminID 社团管理员ID
     * @return 社团管理员的社团信息
     */
    @Override
    public Club viewClubInfo(int clubAdminID) {
        Club club = null;
        try {
            club = jdbc.queryForObject("select c.* from club as c,club_clubAdministrators as cc where cc.clubAdminID=? and cc.clubID=c.clubID", new ClubRowMapper(), clubAdminID);
        } catch (DataAccessException e) {
        }
        return club;
    }

    /**
     * 更新社团管理员的社团信息
     *
     * @param clubAdminID 社团管理员ID
     * @param club        新的社团信息
     * @return 是否更新成功
     */
    @Override
    public boolean updateClubInfo(int clubAdminID, Club club) {
        int clubID = jdbc.update("select clubID from club_clubAdministrators where clubAdminID=?", clubAdminID);
        return jdbc.update(UPDATE_Club, club.getClubName(), club.getCollege(), club.getDescription(), clubID) != 0;
    }

    static class ClubRowMapper implements RowMapper<Club> {
        /**
         * 将数据库查询结果映射到Club对象
         *
         * @param rs 查询结果集
         * @param i 行号
         * @return Club对象
         * @throws SQLException SQL异常
         */
        public Club mapRow(ResultSet rs, int i) throws SQLException {
            Club club = new Club();
            // 从结果集获取club字段并构造Club对象
            club.setClubID(rs.getInt("clubID"));
            club.setClubName(rs.getString("clubName"));
            club.setCollege(rs.getString("college"));
            club.setDescription(rs.getString("description"));
            club.setCreateTime(rs.getTimestamp("createTime"));
            return club;
        }
    }
}
